import json
import re
import threading
import time
from queue import Queue

import random
import datetime
import pymysql
import requests
from lxml import etree
from openpyxl import Workbook
from requests.packages.urllib3.exceptions import InsecureRequestWarning

# 禁用安全请求警告
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)


# 获取get请求的结果 is_text:是否获取文本，False:二进制数据，True:文本数据
def get_request_result(url, header=None, param=None, is_text=False):
    try:
        if param is not None:
            url = url + '?' + param
        response = requests.get(url=url, headers=header, verify=False)
        sleep_time = random.random() + 0.5
        time.sleep(sleep_time)  # 下一次请求时，线程阻塞0.5-1秒
        if is_text:
            return response.text.strip()
        return response.content.decode('utf-8').strip()
    except Exception as e:
        print('请求url：{}，请求参数：{}，异常信息：{}'.format(url, param, str(e)))
        return '请求异常'


# 连接数据库
def get_connect():
    # 连接数据库
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='123456',
        db='demonone',
        charset='utf8mb4'
    )
    return connect


# 获取游标
def get_cursor(connect):
    # 获取游标
    cursor = connect.cursor()
    return cursor


# 写入数据库,电影信息
def insert_film_info(film_id, film_name, film_type, film_area, film_year, film_img, film_type_name, film_detail_url, film_protagonist, film_introduce):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_insert = ''
    try:
        now_time = datetime.datetime.now()
        # 判断是否存在
        sql_select = "SELECT * FROM xcyy_film_info WHERE film_id = %s"
        data_select = film_id
        cursor.execute(sql_select, data_select)
        rows = cursor.fetchone()
        if rows is not None:
            print("重复写入：" + film_name)
            return False
        # 写入数据库
        sql_insert = "INSERT INTO xcyy_film_info (film_id, film_name, film_type, film_area, film_year, film_img, film_type_name, film_detail_url, film_protagonist, film_introduce,film_insert_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        data = (film_id, film_name, film_type, film_area, film_year, film_img, film_type_name, film_detail_url, film_protagonist, film_introduce, now_time)
        cursor.execute(sql_insert, data)
        connect.commit()
        return True
    except Exception as ex:
        print('视频信息写入失败：{},sql_insert:{},异常信息：{}'.format(film_name, sql_insert, str(ex)))
        return False
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


# 写入m3u8文件
def insert_film_play_m3u8(film_id, film_play_url, film_play_line, film_episode, play_url_m3u8):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_insert = ''
    try:
        now_time = datetime.datetime.now()
        # 判断是否存在
        sql_select = "SELECT * FROM xcyy_film_play_m3u8 WHERE film_id = %s AND play_url_m3u8 = %s"
        data_select = (film_id, play_url_m3u8)
        cursor.execute(sql_select, data_select)
        rows = cursor.fetchone()
        film_episode = '第{}集'.format(film_episode)
        if rows is not None:
            print("重复写入：{},{}".format(str(film_episode), play_url_m3u8))
            return False
        # 写入数据库
        sql_insert = "INSERT INTO xcyy_film_play_m3u8 (film_id,film_play_url,film_play_line,film_episode,play_url_m3u8,film_insert_time) VALUES (%s, %s, %s, %s, %s, %s)"
        data = (film_id, film_play_url, film_play_line, film_episode, play_url_m3u8, now_time)
        cursor.execute(sql_insert, data)
        connect.commit()
        return True
        # print('写入成功：{},{}'.format(str(film_episode), play_url_m3u8))
    except Exception as ex:
        print('写入失败：{},sql_insert：{},异常信息：{}'.format(film_name, sql_insert, str(ex)))
        return False
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


# 判断一个月之内采集过的数据不采集 film_id:视频id
def collect_time_by_one_month(film_id):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_insert = ''
    try:
        # 判断是否存在
        sql_select = "SELECT * FROM xcyy_film_info WHERE film_id = %s"
        data_select = film_id
        cursor.execute(sql_select, data_select)
        res = cursor.fetchone()
        if res is None:
            return False  # 根据film_id查询无记录 还没写入
        else:
            result = dict(zip([k[0] for k in cursor.description], res))
            # print('查询成功：{},data：{}'.format(film_id, result))
            insert_time = result.get('film_insert_time')
            timeStruct = time.strptime(insert_time, '%Y-%m-%d %H:%M:%S.%f')
            insert_timeStamp = int(time.mktime(timeStruct))
            now_timeStamp = int(time.time())  # 当前时间戳，秒级
            diff_timeStamp = now_timeStamp - insert_timeStamp
            one_month = 31 * 24 * 60 * 60
            # 判断是否大于一个月
            if diff_timeStamp < one_month:
                return True
            else:
                return False
    except Exception as ex:
        print('查询失败：{},sql_insert：{},异常信息：{}'.format(film_id, sql_insert, str(ex)))
        return True
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


# 记录请求解析失败日志
def insert_error_log(film_id, film_name, film_play_url, error_message):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_insert = ''
    try:
        now_time = datetime.datetime.now()
        # 写入数据库
        sql_insert = "INSERT INTO xcyy_error_log(film_id,film_name,film_play_url,error_message,insert_time) VALUES (%s, %s, %s, %s, %s)"
        data = (film_id, film_name, film_play_url, error_message, now_time)
        cursor.execute(sql_insert, data)
        connect.commit()
    except Exception as ex:
        print('写入失败：{},sql_insert：{},异常信息：{}'.format(film_name, sql_insert, str(ex)))
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


# 修改状态值
def update_state(_id):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_update = ''
    try:
        # 写入数据库
        sql_update = "update xcyy_error_log set state = 1 where id = %s"
        data = int(_id)
        cursor.execute(sql_update, data)
        connect.commit()
        print('修改成功，id：{}'.format(_id))
    except Exception as ex:
        print('修改失败：{},sql_insert：{},异常信息：{}'.format(_id, sql_update, str(ex)))
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


def get_play_url(detail_url_queue):
    sdf_time = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
    save_path = 'C:\\Users\\Administrator\\Desktop\\星辰影院\\名侦探柯南资源列表' + str(sdf_time) + '.xlsx'
    host = 'http://www.cu518.com'
    if host.startswith('http://'):
        host = 'https://www.cu518.com'
    # 写入excel
    workbook = Workbook()
    u = 'https://www.cu518.com/xingchen_detail/131044.html'
    detail_url_queue.put(u)
    while not detail_url_queue.empty():
        try:
            detail_url = detail_queue.get()
            detail_queue.task_done()
            # response = requests.get(url=detail_url).text
            response = get_request_result(url=detail_url, is_text=True)
            time.sleep(0.2)

            _lxml = etree.HTML(response)
            film_name = _lxml.xpath('//div/h1/text()')  # 电影名字
            film_protagonist = _lxml.xpath('//p/a/text()')  # 主演
            film_introduce = re.findall('<span class="detail-content" style="display: none;">(.*)</span>', response)  # 简介
            film_img = _lxml.xpath('//div/a/img/@data-original')  # 封面图片

            # 类型 地区 年份
            temp_str = re.findall('</span>(.*)<span class="split-line">', response)
            type_str = re.findall('<span class="text-muted">(.*)<span class="split-line">', response)[0].replace('\t', '').replace('</span>', '')
            film_type = str(type_str).split('：')[1]
            area_str = re.findall('<span class="text-muted hidden-xs">(.*)<span class="split-line">', response)[0].replace('\t', '').replace('</span>', '')
            film_area = str(area_str).split('：')[1]
            year_str = re.findall('<span class="text-muted hidden-xs">(.*)', response)[1].replace('\t', '').replace('</span>', '')
            film_year = str(year_str).split('：')[1].replace('\r', '')
            # 创建工作表
            worksheet = workbook.create_sheet(title=film_name[0])

            film_name = film_name[0]
            protagonist = ''
            for pt in film_protagonist:
                protagonist += pt + ','
            protagonist = protagonist[0:len(protagonist) - 6]
            film_introduce = film_introduce[0]
            worksheet.cell(1, 1, 'id')
            worksheet.cell(1, 2, 1)
            worksheet.cell(2, 1, '名字')
            worksheet.cell(2, 2, film_name)
            worksheet.cell(3, 1, '类型')
            worksheet.cell(3, 2, film_type)
            worksheet.cell(4, 1, '地区')
            worksheet.cell(4, 2, film_area)
            worksheet.cell(5, 1, '年份')
            worksheet.cell(5, 2, film_year)
            worksheet.cell(6, 1, '主演')
            worksheet.cell(6, 2, protagonist)
            worksheet.cell(7, 1, '简介')
            worksheet.cell(7, 2, film_introduce)
            row_line1 = 8
            row_line2 = 8
            row_line3 = 8
            row_line4 = 8
            row_line = 8
            worksheet.cell(row_line1, 1, '播放地址')
            # 播放路线
            play_line_cn = _lxml.xpath('//div/h3/text()')
            detail_play_url = _lxml.xpath('//div/ul/li/a/@href')
            print(detail_play_url)
            for url in detail_play_url:
                try:
                    # 判断是否为播放链接
                    flag = 'play' in url
                    if flag:
                        temp = url.split('/')[2]
                        film_id = temp.split('-')[0]
                        play_line = int(temp.split('-')[1])
                        episode = str(temp.split('-')[2]).split('.')[0]  # 集数
                        url = host + url
                        worksheet.cell(1, 2, film_id)
                        # 抓取HTML中的.m3u8文件
                        # response_m3u8_temp = requests.get(url=url).text
                        response_m3u8_temp = get_request_result(url=url, is_text=True)
                        time.sleep(0.2)

                        # res = json.loads(re.findall('var player_x10d26=(.+);n', response.body.decode('utf-8'))[0])
                        response_m3u8 = response_m3u8_temp.replace('}', '};')
                        result_temp = re.findall('var player_x10d26=(.*);', response_m3u8)
                        result = str(result_temp).replace('\'', '')
                        # print(result)
                        result_data = json.loads(result)
                        play_url_m3u8 = result_data[0].get('url').replace('\\', '')
                        play_url_id = result_data[0].get('id')
                        # print(play_url_m3u8)
                        # id,film_id,film_name,film_type,film_area,
                        # film_year,film_img,film_type_name,film_detail_url,
                        # film_protagonist,film_introduce
                        if play_line == 1:
                            row_line1 += 1
                            value = worksheet.cell(8, 2).value
                            if value is None:
                                worksheet.cell(8, 2, '集数')
                                worksheet.cell(8, 3, '线路' + str(play_line))
                            worksheet.cell(row_line1, 2, '第' + str(episode) + '集')
                            worksheet.cell(row_line1, 3, play_url_m3u8)
                        elif play_line == 2:
                            row_line2 += 1
                            value = worksheet.cell(8, 4).value
                            if value is None:
                                worksheet.cell(8, 4, '集数')
                                worksheet.cell(8, 5, '线路' + str(play_line))
                            worksheet.cell(row_line2, 4, '第' + str(episode) + '集')
                            worksheet.cell(row_line2, 5, play_url_m3u8)
                        elif play_line == 3:
                            row_line3 += 1
                            value = worksheet.cell(8, 6).value
                            if value is None:
                                worksheet.cell(8, 6, '集数')
                                worksheet.cell(8, 7, '线路' + str(play_line))
                            worksheet.cell(row_line3, 6, '第' + str(episode) + '集')
                            worksheet.cell(row_line3, 7, play_url_m3u8)
                        elif play_line == 4:
                            row_line4 += 1
                            value = worksheet.cell(8, 8).value
                            if value is None:
                                worksheet.cell(8, 8, '集数')
                                worksheet.cell(8, 9, '线路' + str(play_line))
                            worksheet.cell(row_line4, 8, '第' + str(episode) + '集')
                            worksheet.cell(row_line4, 9, play_url_m3u8)
                        else:
                            row_line += 1
                            value = worksheet.cell(8, 10).value
                            if value is None:
                                worksheet.cell(8, 10, '集数')
                                worksheet.cell(8, 11, '默认线路')
                            worksheet.cell(row_line, 10, '第' + str(episode) + '集')
                            worksheet.cell(row_line, 11, play_url_m3u8)
                        print('抓取成功,名字：{},集数：第{}集,地址：{}'.format(film_name, episode, play_url_m3u8))
                except Exception as ex:
                    print('{}电影名字：{}，m3u8_url：{}，异常信息：{}{}'.format(('*' * 15), film_name, url, str(ex), ('*' * 15)))
                    continue
            workbook.save(save_path)
            count = int(row_line1) + int(row_line2) + int(row_line3) + int(row_line4) + int(row_line) - 40
            print('总行数：{}'.format(count))

        except Exception as e:
            print('{}电影名字：{}，detail_url：{}，异常信息：{}{}'.format(('*' * 15), film_name, detail_url, str(e), ('*' * 15)))
            continue


# 查询出异常的url
def find_state_url(play_url_queue, detail_url_queue):
    # 连接数据库获取游标
    connect = get_connect()
    cursor = get_cursor(connect)
    sql_insert = ''
    try:
        sql_select = "SELECT * FROM xcyy_error_log e WHERE e.state = %s"
        data_select = 0
        cursor.execute(sql_select, data_select)
        res = cursor.fetchall()
        result = [dict(zip([k[0] for k in cursor.description], row)) for row in res]
        # print(result)
        for r in result:
            _id = r.get('id')
            film_name = r.get('film_name')
            play_url = r.get('film_play_url')
            id_play_url = '{}@{}@{}'.format(_id, film_name, play_url)
            if 'play' in play_url:
                play_url_queue.put(id_play_url)
            else:
                detail_url_queue.put(id_play_url)
    except Exception as ex:
        print('查询失败：sql_insert:{},异常信息：{}'.format(sql_insert, str(ex)))
    finally:
        # 关闭连接
        cursor.close()
        connect.close()


# 单独开一个线程轮询出现异常的数据
def insert_play_url(detail_url_queue):
    host = 'http://www.cu518.com'
    if host.startswith('http://'):
        host = 'https://www.cu518.com'
    # u = 'https://www.cu518.com/xingchen_detail/126777.html'
    # detail_url_queue.put(u)
    while not detail_url_queue.empty():
        film_id = ''  # 电影id
        film_name = ''  # 电影名字
        detail_url = ''  # 电影详情url
        try:
            detail_url = detail_url_queue.get()
            detail_url_queue.task_done()
            _id = detail_url.split('@')[0]
            film_name = detail_url.split('@')[1]
            detail_url = detail_url.split('@')[2]

            # response = requests.get(url=detail_url).text
            response = get_request_result(url=detail_url, is_text=True)
            time.sleep(0.2)

            _lxml = etree.HTML(response)
            film_name = _lxml.xpath('//div/h1/text()')  # 电影名字
            film_protagonist = _lxml.xpath('//p/a/text()')  # 主演
            film_introduce = re.findall('<span class="detail-content" style="display: none;">(.*)</span>', response)  # 简介
            # film_img = _lxml.xpath('//div/a/img/@data-original')  # 封面图片
            film_img = _lxml.xpath('//div/a/img/@data-original')[0]  # 封面图片

            # 类型 地区 年份
            temp_str = re.findall('</span>(.*)<span class="split-line">', response)
            type_str = re.findall('<span class="text-muted">(.*)<span class="split-line">', response)[0].replace('\t', '').replace('</span>', '')
            film_type = str(type_str).split('：')[1]
            area_str = re.findall('<span class="text-muted hidden-xs">(.*)<span class="split-line">', response)[0].replace('\t', '').replace('</span>', '')
            film_area = str(area_str).split('：')[1]
            year_str = re.findall('<span class="text-muted hidden-xs">(.*)', response)[1].replace('\t', '').replace('</span>', '')
            film_year = str(year_str).split('：')[1].replace('\r', '')

            film_name = film_name[0]
            protagonist = ''
            for pt in film_protagonist:
                protagonist += pt + ','
            protagonist = protagonist[0:len(protagonist) - 6]
            if len(film_introduce) == 0:
                film_introduce.append('暂无简介')
            film_introduce = film_introduce[0]

            temp_id = detail_url.split('/')[4]
            film_id = int(temp_id.split('.')[0])

            # 写入电影信息
            bool_one_month = collect_time_by_one_month(film_id)
            if bool_one_month:
                print('视频id：{}，视频名字：{}{}该资源一个月内采集过{}'.format(film_id, film_name, ('*' * 15), ('*' * 15)))
                continue  # 一个月之内采集过该资源,不采集,进入下一次循环
            bool_info = insert_film_info(film_id, film_name, film_type, film_area, film_year, film_img, '电影', detail_url, protagonist, film_introduce)
            if bool_info:
                print('视频信息写入成功：{}'.format(film_name))
            else:
                print('视频信息写入失败：{}'.format(film_name))
                continue  # 信息写入失败，进入下一次请求
            # 播放路线
            play_line_cn = _lxml.xpath('//div/h3/text()')
            detail_play_url = _lxml.xpath('//div/ul/li/a/@href')
            # print(detail_play_url)
            count = 0
            for url in detail_play_url:
                m3u8_url = ''
                try:
                    # 判断是否为播放链接
                    flag = 'play' in url
                    if flag:
                        temp = url.split('/')[2]
                        film_id = temp.split('-')[0]
                        play_line = int(temp.split('-')[1])
                        episode = str(temp.split('-')[2]).split('.')[0]  # 集数
                        m3u8_url = host + url

                        # 抓取HTML中的.m3u8文件
                        # response_m3u8_temp = requests.get(url=url).text
                        response_m3u8_temp = get_request_result(url=m3u8_url, is_text=True)
                        time.sleep(0.2)

                        # res = json.loads(re.findall('var player_x10d26=(.+);n', response.body.decode('utf-8'))[0])
                        response_m3u8 = response_m3u8_temp.replace('}', '};')
                        result_temp = re.findall('var player_x10d26=(.*);', response_m3u8)
                        result = str(result_temp).replace('\'', '')
                        # print(result)
                        result_data = json.loads(result)
                        play_url_m3u8 = result_data[0].get('url').replace('\\', '')
                        play_url_id = result_data[0].get('id')
                        # print(play_url_m3u8)
                        # 写入m3u8文件
                        bool_m3u8 = insert_film_play_m3u8(film_id, m3u8_url, play_line, episode, play_url_m3u8)
                        if bool_m3u8:
                            print('写入成功,电影名字：{},线路：{},集数：第{}集,地址：{}'.format(film_name, play_line, episode, play_url_m3u8))
                            count += 1
                            update_state(_id)  # 修改状态
                        else:
                            print('写入失败,电影名字：{},线路：{},集数：第{}集,地址：{}'.format(film_name, play_line, episode, play_url_m3u8))
                except Exception as ex:
                    insert_error_log(film_id, film_name, m3u8_url, str(ex))  # 请求异常解析异常
                    print('{}电影名字：{}，m3u8_url：{}，异常信息：{}{}'.format(('*' * 15), film_name, m3u8_url, str(ex), ('*' * 15)))
                    continue
            print('电影名字：{},总行数：{}'.format(film_name, count))
        except Exception as e:
            insert_error_log(film_id, film_name, detail_url, str(e))  # 请求异常解析异常
            print('{}电影名字：{}，detail_url：{}，异常信息：{}{}'.format(('*' * 15), film_name, detail_url, str(e), ('*' * 15)))
            continue


# 单独开一个线程轮询出现异常的数据
def insert_play_url1(play_url_queue):
    host = 'http://www.cu518.com'
    if host.startswith('http://'):
        host = 'https://www.cu518.com'
    # u = 'https://www.cu518.com/xingchen_play/117381-4-15.html'
    # play_url_queue.put(u)
    while not play_url_queue.empty():
        film_id = ''  # 电影id
        film_name = ''  # 电影名字
        m3u8_url = ''  # 电影详情url
        try:
            play_url = play_url_queue.get()
            play_url_queue.task_done()
            _id = play_url.split('@')[0]
            film_name = play_url.split('@')[1]
            m3u8_url = play_url.split('@')[2]

            temp = m3u8_url.split('/')[4]
            film_id = temp.split('-')[0]
            play_line = int(temp.split('-')[1])
            episode = str(temp.split('-')[2]).split('.')[0]  # 集数

            # 抓取HTML中的.m3u8文件
            # response_m3u8_temp = requests.get(url=url).text
            response_m3u8_temp = get_request_result(url=m3u8_url, is_text=True)
            time.sleep(0.2)

            # res = json.loads(re.findall('var player_x10d26=(.+);n', response.body.decode('utf-8'))[0])
            response_m3u8 = response_m3u8_temp.replace('}', '};')
            result_temp = re.findall('var player_x10d26=(.*);', response_m3u8)
            result = str(result_temp).replace('\'', '')
            # print(result)
            result_data = json.loads(result)
            play_url_m3u8 = result_data[0].get('url').replace('\\', '')
            play_url_id = result_data[0].get('id')
            # print(play_url_m3u8)
            # 写入m3u8文件
            bool_m3u8 = insert_film_play_m3u8(film_id, m3u8_url, play_line, episode, play_url_m3u8)
            if bool_m3u8:
                print('写入成功,电影名字：{},线路：{},集数：第{}集,地址：{}'.format(film_name, play_line, episode, play_url_m3u8))
                update_state(_id)  # 修改状态
            else:
                print('写入失败,电影名字：{},线路：{},集数：第{}集,地址：{}'.format(film_name, play_line, episode, play_url_m3u8))
        except Exception as ex:
            insert_error_log(film_id, film_name, m3u8_url, str(ex))  # 请求异常解析异常
            print('{}电影名字：{}，m3u8_url：{}，异常信息：{}{}'.format(('*' * 15), film_name, m3u8_url, str(ex), ('*' * 15)))
            continue


if __name__ == '__main__':
    detail_queue = Queue(maxsize=1000)  # 详情队列
    play_queue = Queue(maxsize=500)  # play_url队列

    # th = threading.Thread(target=get_play_url, args=(queue,))
    thread_find = threading.Thread(target=find_state_url, args=(play_queue, detail_queue))
    thread_find.start()

    detail_thread = threading.Thread(target=insert_play_url, args=(detail_queue,))
    time.sleep(1)
    detail_thread.start()

    play_thread = threading.Thread(target=insert_play_url1, args=(play_queue,))
    time.sleep(1)
    play_thread.start()

    thread_find.join()
    detail_thread.join()
    play_thread.join()
